package cn.sypt.yuhao.dao;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;

import cn.sypt.yuhao.domain.Acategory;
import cn.sypt.yuhao.domain.Bcategory;
import cn.sypt.yuhao.domain.Category;
import cn.sypt.yuhao.domain.Comments;
import cn.sypt.yuhao.domain.Exam;
import cn.sypt.yuhao.domain.Manager;
import cn.sypt.yuhao.domain.Reply;
import cn.sypt.yuhao.domain.User;
import cn.sypt.yuhao.domain.Video;
import cn.sypt.yuhao.domain.Words;
import cn.sypt.yuhao.model.DBcon;
import cn.sypt.yuhao.model.Sdzfc;
import cn.sypt.yuhao.model.Tool;
import cn.sypt.yuhao.model.Uuid;

import com.mysql.jdbc.Connection;

public class ManagerBO {
	
	Connection con=null;
	PreparedStatement ps=null;
	ResultSet rs=null;
	
	boolean a;
	
	//管理员登陆
	public boolean checkManager(String mname,String p)
	{
		a=false;
	
		try {
			
			con=(Connection) new DBcon().getConnection();
			
			String sql="select mpw from manager where mname='"+mname+"'";
			ps=con.prepareStatement(sql);
			
			rs=ps.executeQuery();
			
			if(rs.next())
			{
				String mpw=rs.getString(1);
				if(mpw.equals(p))
				{
				System.out.println("mname="+mname+",mpw="+mpw);
				a=true;
				}
			}
		} catch (Exception e) {
			e.printStackTrace();
		}finally
		{
			this.close();
		}
		return a;
	}
	
	//获得manager的信息
	public Manager getManager(String mname)
	{
		Manager man=new Manager();
		
		try {
			
			con=(Connection) new DBcon().getConnection();
			
			String sql="select * from manager where mname='"+mname+"'";
			ps=con.prepareStatement(sql);
			rs=ps.executeQuery();
			
			if(rs.next())
			{
				man.setMname(rs.getString(1));
				man.setMpw(rs.getString(2));
				man.setMpermitted(rs.getString(3));
			}
			
		} catch (Exception e) {
			e.printStackTrace();
		}finally
		{
			this.close();
		}
		
		return man;
		
	}
	
	//得到user的集合
	public ArrayList getUsers()
	{
		ArrayList al=new ArrayList();
		try {
			
            con=(Connection) new DBcon().getConnection();
			
			String sql="select uname,uemail,uphone,utime,level,uid from user";
			ps=con.prepareStatement(sql);
			rs=ps.executeQuery();
			
			while(rs.next())
			{
				User user=new User();
				user.setUname(rs.getString(1));
				user.setUemail(rs.getString(2));
				user.setUphone(rs.getString(3));
				user.setUtime(rs.getString(4));
				user.setLevel(rs.getShort(5));
				user.setUid(rs.getString(6));
				al.add(user);
			}
			
		} catch (Exception e) {
			e.printStackTrace();
		}finally
		{
			this.close();
		}
		
		return al;
	}
	
	//管理用户等级升或降级或者删除用户
	public boolean userDj(String flag1,String uid)
	{
		boolean a=false;
		String sql="";
		try {
			
			 con=(Connection) new DBcon().getConnection();
			 
			if(flag1.equals("sj"))
			{
		      sql="update user set level=1 where uid='"+uid+"'";
			}else if(flag1.equals("jj"))
			{
				sql="update user set level=0 where uid='"+uid+"'";
			}else if(flag1.equals("dl"))
			{
				sql="delete from user where uid='"+uid+"'";
			}
		     ps=con.prepareStatement(sql);
		     int num=ps.executeUpdate();
		     
		     if(num==1)
		     {
		    	 a=true;
		     }
		     
			
		} catch (Exception e) {
			e.printStackTrace();
		}finally
		{
			this.close();
		}
		
		return a;
	}
	
	//得到评论的所有集合
	public ArrayList getComments()
	{
		ArrayList al=new ArrayList();
		try {
			
            con=(Connection) new DBcon().getConnection();
			
			String sql="select * from comment";
			ps=con.prepareStatement(sql);
			rs=ps.executeQuery();
			while(rs.next())
			{
				Comments com=new Comments();
				com.setUid(rs.getString(1));
				com.setSpid(rs.getString(2));
				com.setPtime(rs.getString(3));
				com.setPstr(new Tool().getSub(15, rs.getString(4)));
				com.setCid(rs.getString(5));
				al.add(com);
				
			}
			
		} catch (Exception e) {
			e.printStackTrace();
		}finally
		{
			this.close();
		}
		return al;
		
	}
	
	//通过cid删除评论
	public boolean dlComment(String cid)
	{		
		boolean a=false;
		try {
			
			con=(Connection) new DBcon().getConnection();
			
			String sql="delete from comment where cid='"+cid+"'";
			ps=con.prepareStatement(sql);
			int num=ps.executeUpdate();
			if(num==1)
			{
				a=true;
			}
			
		} catch (Exception e) {
			e.printStackTrace();
		}finally
		{
			this.close();
		}
		
		return a;
	}
	
	//删除评论时删掉所有关与此评论的回复
	public void dlPlAllReply(String cid)
	{
		try {
			
            con=(Connection) new DBcon().getConnection();
			
			String sql="delete from reply where cid='"+cid+"'";
			ps=con.prepareStatement(sql);
			ps.executeUpdate();
			
		} catch (Exception e) {
			e.printStackTrace();
		}finally
		{
			this.close();
		}
	}
	
	//得到所有回复内容通过cid
	public ArrayList getReplys(String cid)
	{
		ArrayList al=new ArrayList();
		try {
			
            con=(Connection) new DBcon().getConnection();
			
			String sql="select * from reply where cid='"+cid+"'";
			ps=con.prepareStatement(sql);
			rs=ps.executeQuery();
			while(rs.next())
			{
				Reply re=new Reply();
				re.setRid(rs.getString(1));
				re.setCid(rs.getString(2));
				re.setUid(rs.getString(3));
				re.setRstr(new Tool().getSub(15,rs.getString(4)));
				re.setRtime(rs.getString(5));
				al.add(re);
			}
			
		} catch (Exception e) {
			e.printStackTrace();
		}finally
		{
			this.close();
		}
		return al;
	}
	
	//删除回复通过rid
	public void Dlreply(String rid)
	{
		try {
						
		    con=(Connection) new DBcon().getConnection();			
			String sql="delete from reply where rid='"+rid+"'";
			ps=con.prepareStatement(sql);
			int num=ps.executeUpdate();
			
		} catch (Exception e) {
			e.printStackTrace();
		}finally
		{
			this.close();
		}
	}
	

	
	//通过uid得到user	
		public User getUser(String uid)
		{
			User user=new User();
			
			try {
				
				con=(Connection) new DBcon().getConnection();
				
				String sql="select * from user where uid='"+uid+"'";
				ps=con.prepareStatement(sql);
				rs=ps.executeQuery();
				
				if(rs.next())
				{
					user.setUid(rs.getString(1));
					user.setUname(rs.getString(2));
					user.setUpass(rs.getString(3));
					user.setUemail(rs.getString(4));
					user.setUaddress(rs.getString(5));
					user.setUbirth(rs.getString(6));
					user.setUphone(rs.getString(7));
					user.setUimage(rs.getString(8));
					user.setActive(rs.getString(9));
					user.setLevel(rs.getShort(10));
					user.setUtime(rs.getString(11));
				}
				
			} catch (Exception e) {
				e.printStackTrace();
			}finally
			{
				this.close();
			}
			
			return user;
			
		}
		
		//通过spid得到video
		public Video getVideo(String spid)
		{
			Video video=new Video();
			try {
				
                con=(Connection) new DBcon().getConnection();
				
				String sql="select * from video where spid='"+spid+"'";
				ps=con.prepareStatement(sql);
				rs=ps.executeQuery();
				
				while(rs.next())
				{					
					video.setSpid(rs.getString(1));
					video.setSpname(rs.getString(2));
					video.setSpstr(rs.getString(3));
					video.setSptime(rs.getString(4));
					video.setSpurl(rs.getString(5));
					video.setSptpurl(rs.getString(6));
					video.setSpxz(rs.getShort(7));
					video.setSpdz(rs.getShort(8));
					video.setBid(rs.getString(9));
					video.setFid(rs.getString(10));
					video.setUname(rs.getString(11));
					video.setSpwdurl(rs.getString(12));
					video.setSpsh(rs.getShort(13));
				}
				
			} catch (Exception e) {
				e.printStackTrace();
			}finally
			{
				this.close();
			}
			
			return video;
		}
		
	//得到所有视频的集合
		public ArrayList getVideos()
		{
			ArrayList al=new ArrayList();
			try {
				
                con=(Connection) new DBcon().getConnection();
				
				String sql="select * from video where spsh=0 or spsh=1";
				ps=con.prepareStatement(sql);
				rs=ps.executeQuery();
				
				while(rs.next())
				{	
					Video video=new Video();
					video.setSpid(rs.getString(1));
					video.setSpname(rs.getString(2));
					video.setSpstr(new Sdzfc().getSub(rs.getString(3)));
					video.setSptime(rs.getString(4));
					video.setSpurl(rs.getString(5));
					video.setSptpurl(rs.getString(6));
					video.setSpxz(rs.getShort(7));
					video.setSpdz(rs.getShort(8));
					video.setBid(rs.getString(9));
					video.setFid(rs.getString(10));
					video.setUname(rs.getString(11));
					video.setSpwdurl(rs.getString(12));
					video.setSpsh(rs.getShort(13));
					al.add(video);
				}
				
			} catch (Exception e) {
				e.printStackTrace();
			}finally
			{
				this.close();
			}
			
			return al;
		}
		
		//对视频的审核和删除
		public void videoShdl(String flag1,String spid)
		{
			String sql="";
			try {
				
				    con=(Connection) new DBcon().getConnection();
					//通过审核
					if(flag1.equals("tg"))
					{
						sql="update video set spsh=1 where spid='"+spid+"'";
					}
					//未通过
					else if(flag1.equals("btg"))
					{
						sql="update video set spsh=0 where spid='"+spid+"'";
					}
					//删除视频
					else if(flag1.equals("dl"))
					{
						sql="update video set spsh=3 where spid='"+spid+"'";
					}
					ps=con.prepareStatement(sql);
					int num=ps.executeUpdate();
					
				
			} catch (Exception e) {
				e.printStackTrace();
			}finally
			{
				this.close();
			}
		}
		
		//得到cate的总类
		public ArrayList getSycate()
		{
			ArrayList al=new ArrayList();
			
			try {
				
                con=(Connection) new DBcon().getConnection();
				
				String sql="select * from category order by fnum asc";
				ps=con.prepareStatement(sql);
				rs=ps.executeQuery();
				
				while(rs.next())
				{
					Category cate=new Category();
					cate.setFid(rs.getString(1));
					cate.setFname(rs.getString(2));
					cate.setFnum(rs.getString(3));
					al.add(cate);
					System.out.println(cate.getFnum());
				}
				
			} catch (Exception e) {
				e.printStackTrace();
			}finally
			{
				this.close();
			}
			
			return al;
		}
		
		//添加，删除，修改cate分类
		public void tdxCategory(String flag1,String fid,String fname,String fnum)
		{
			String sql="";
			try {
				
                con=(Connection) new DBcon().getConnection();
				if(flag1.equals("dl"))
				{
					sql="delete from category where fid='"+fid+"'";
				}
				else if(flag1.equals("xg"))
				{
					sql="update category set fname='"+fname+"',fnum='"+fnum+"' where fid='"+fid+"'";
				}
				else if(flag1.equals("tj"))
				{
					String fid1=new Uuid().uuid();
					sql="insert into category values('"+fid1+"','"+fname+"','"+fnum+"');";
				}
			
				ps=con.prepareStatement(sql);
				ps.executeUpdate();
				
			} catch (Exception e) {
				e.printStackTrace();
			}finally
			{
				this.close();
			}
		}
		
		
		//得到acate的集合
		public ArrayList getSyacate(String fid)
		{
			ArrayList al=new ArrayList();
			try {
				
                con=(Connection) new DBcon().getConnection();
				
				String sql="select * from acategory where fid='"+fid+"'";
				ps=con.prepareStatement(sql);
				rs=ps.executeQuery();
				while(rs.next())
				{
					Acategory acate=new Acategory();
					acate.setAid(rs.getString(1));
					acate.setAname(rs.getString(2));
					acate.setFid(rs.getString(3));
					al.add(acate);
				}
				
			} catch (Exception e) {
				e.printStackTrace();
			}finally
			{
				this.close();
			}
			
			return al;
		}
		
		//添加，删除，修改acate分类
		public void tdxACategory(String flag1,String aid,String aname,String fid)
		{
			String sql="";
			try {
				
                con=(Connection) new DBcon().getConnection();
				if(flag1.equals("dl"))
				{
					sql="delete from acategory where aid='"+aid+"'";
				}
				else if(flag1.equals("xg"))
				{
					sql="update acategory set aname='"+aname+"' where aid='"+aid+"'";
				}
				else if(flag1.equals("tj"))
				{
					String aid1=new Uuid().uuid();
					sql="insert into acategory values('"+aid1+"','"+aname+"','"+fid+"');";
				}
			
				ps=con.prepareStatement(sql);
				ps.executeUpdate();
				
			} catch (Exception e) {
				e.printStackTrace();
			}finally
			{
				this.close();
			}
		}
		
		
		//得到bcate的集合
		public ArrayList getSybcate(String aid)
		{
			ArrayList al=new ArrayList();
			try {
				
                con=(Connection) new DBcon().getConnection();
				
				String sql="select * from bcategory where aid='"+aid+"'";
				ps=con.prepareStatement(sql);
				rs=ps.executeQuery();
				while(rs.next())
				{
					Bcategory bcate=new Bcategory();
					bcate.setBid(rs.getString(1));
					bcate.setBname(rs.getString(2));
					bcate.setAid(rs.getString(3));
					al.add(bcate);
				}
				
			} catch (Exception e) {
				e.printStackTrace();
			}finally
			{
				this.close();
			}
			
			return al;
		}

		//添加，删除，修改bcate分类
		public void tdxBCategory(String flag1,String bid,String bname,String aid)
		{
			String sql="";
			try {
				
                con=(Connection) new DBcon().getConnection();
				if(flag1.equals("dl"))
				{
					sql="delete from bcategory where bid='"+bid+"'";
				}
				else if(flag1.equals("xg"))
				{
					
					sql="update bcategory set bname='"+bname+"'where bid='"+bid+"'";
					System.out.println(bname+","+bid);
				}
				else if(flag1.equals("tj"))
				{
					String bid1=new Uuid().uuid();
					sql="insert into bcategory values('"+bid1+"','"+bname+"','"+aid+"');";
				}
			
				ps=con.prepareStatement(sql);
				ps.executeUpdate();
				
			} catch (Exception e) {
				e.printStackTrace();
			}finally
			{
				this.close();
			}
		}
		
		//得到敏感词汇的集合
		public ArrayList getMgch()
		{
			ArrayList al=new ArrayList();
			try {
				
                con=(Connection) new DBcon().getConnection();
				
				String sql="select * from words";
				ps=con.prepareStatement(sql);
				rs=ps.executeQuery();
				while(rs.next())
				{
					Words w=new Words();
					w.setWid(rs.getString(1));
					w.setWords(rs.getString(2));
					al.add(w);
				}
				
			} catch (Exception e) {
				e.printStackTrace();
			}finally
			{
				this.close();
			}
			
			return al;
		}
		
		//添加，删除，修改敏感词汇
		public void tdxMgch(String flag1,String wid,String words)
		{
			String sql="";
			try {
				
                con=(Connection) new DBcon().getConnection();
				if(flag1.equals("dl"))
				{
					sql="delete from words where wid='"+wid+"'";
				}
				else if(flag1.equals("xg"))
				{
					
					sql="update words set words='"+words+"'where wid='"+wid+"'";
					System.out.println(words+","+wid);
				}
				else if(flag1.equals("tj"))
				{
					String wids=new Uuid().uuid();
					sql="insert into words values('"+wids+"','"+words+"');";
				}
			
				ps=con.prepareStatement(sql);
				ps.executeUpdate();
				
			} catch (Exception e) {
				e.printStackTrace();
			}finally
			{
				this.close();
			}
		}
		
		//通过spid得到该实验的所有习题
				public ArrayList getExamBySpid(String spid)
				{
					ArrayList al=new ArrayList();
					
					try {
						con=(Connection) new DBcon().getConnection();				
						String sql="select * from exam where spid='"+spid+"'";
						ps=con.prepareStatement(sql);
						rs=ps.executeQuery();
						while(rs.next())
						{
							Exam exam=new Exam();
							exam.setSpid(new Sdzfc().getSub5(rs.getString(1)));
							exam.setTid(rs.getString(2));
							exam.setTtm(new Sdzfc().getSub5(rs.getString(3)));
							exam.setTa(new Sdzfc().getSub5(rs.getString(4)));
							exam.setTb(new Sdzfc().getSub5(rs.getString(5)));
							exam.setTc(new Sdzfc().getSub5(rs.getString(6)));
							exam.setTd(new Sdzfc().getSub5(rs.getString(7)));
							exam.setTda(new Sdzfc().getSub5(rs.getString(8)));
							exam.setTtime(rs.getString(9));
							exam.setNote(new Sdzfc().getSub5(rs.getString(10)));
							al.add(exam);
						}
					} catch (Exception e) {
						e.printStackTrace();
					}finally
					{
						this.close();
					}
					
					return al;
					
				}
				
				//添加，删除，修改习题内容
				public void tdxExam(String flag1,String spid,String tid,String ttm,String ta,String tb,String tc,String td,String tda,String note)
				{
					String sql="";
					try {
						
		                con=(Connection) new DBcon().getConnection();
						if(flag1.equals("dl"))
						{
							sql="delete from exam where tid='"+tid+"'";
						}
						else if(flag1.equals("xg"))
						{
							
							sql="update exam set ttm='"+ttm+"',ta='"+ta+"',tb='"+tb+"',tc='"+tc+"',td='"+td+"',tda='"+tda+"',note='"+note+"' where tid='"+tid+"'";
							
						}
						else if(flag1.equals("tj"))
						{
							String tid1=new Uuid().uuid();
							SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
							String time=df.format(new Date());
							sql="insert into exam values('"+spid+"','"+tid1+"','"+ttm+"','"+ta+"','"+tb+"','"+tc+"','"+td+"','"+tda+"','"+time+"','"+note+"');";
						}
					
						ps=con.prepareStatement(sql);
						ps.executeUpdate();
						
					} catch (Exception e) {
						e.printStackTrace();
					}finally
					{
						this.close();
					}
				}
				
				//通过tid得到exam
				public Exam getExam(String tid)
				{
					Exam exam=new Exam();
					try {
						
						con=(Connection) new DBcon().getConnection();				
						String sql="select * from exam where tid='"+tid+"'";
						ps=con.prepareStatement(sql);
						rs=ps.executeQuery();
						while(rs.next())
						{
							exam.setSpid(rs.getString(1));
							exam.setTid(rs.getString(2));
							exam.setTtm(rs.getString(3));
							exam.setTa(rs.getString(4));
							exam.setTb(rs.getString(5));
							exam.setTc(rs.getString(6));
							exam.setTd(rs.getString(7));
							exam.setTda(rs.getString(8));
							exam.setTtime(rs.getString(9));
							exam.setNote(rs.getString(10));
						}
						
					} catch (Exception e) {
						e.printStackTrace();
					}finally
					{
						this.close();
					}
					
					return exam;
				}
			
	
	//关闭结果集
		public void close()
		{
			try {
				
				if(rs!=null)
				{
					rs.close();
					rs=null;
				}
				if(ps!=null)
				{
					ps.close();
					ps=null;
				}
				if(con!=null)
				{
					con.close();
					con=null;
				}
				
			} catch (Exception e) {
				e.printStackTrace();
			}
		}

}
